Skip Headers

Oracle® Database Application Developer's Guide - Large Objects 10g Release 2 (10.2) Part Number B14249-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 15 LOB APIs for BFILE Operations

This chapter describes APIs for operations that use `BFILE`s. APIs covered in this chapter are listed in Table 15-1.

The following information is given for each operation described in this chapter:

- Usage Notes provide implementation guidelines such as information specific to a given programmatic environment or datatype.

- Syntax refers you to the syntax reference documentation for each supported programmatic environment.

- Examples describe any setup tasks necessary to run the examples given. Demonstration files listed are available in subdirectories under `$ORACLE_HOME/rdbms/demo/lobs/` named `plsql`, `oci`, `vb`, and `java`. The driver program `lobdemo.sql` is in `/plsql` and the driver program `lobdemo.c` is in `/oci`.

  Note: LOB APIs do not support loading data into `BFILE`s. See "Using SQL*Loader to Load LOBs" for details on techniques for loading data into `BFILE`s.

This chapter contains these topics:

- Supported Environments for BFILE APIs

- Accessing BFILEs

- Directory Objects

- BFILENAME and Initialization

- Characteristics of the BFILE Datatype

- BFILE Security

- Loading a LOB with BFILE Data

- Opening a BFILE with OPEN

- Opening a BFILE with FILEOPEN

- Determining Whether a BFILE Is Open Using ISOPEN

- Determining Whether a BFILE Is Open with FILEISOPEN

- Displaying BFILE Data

- Reading Data from a BFILE

- Reading a Portion of BFILE Data Using SUBSTR

- Comparing All or Parts of Two BFILES

- Checking If a Pattern Exists in a BFILE Using INSTR

- Determining Whether a BFILE Exists

- Getting the Length of a BFILE

- Assigning a BFILE Locator

- Getting Directory Object Name and File Name of a BFILE

- Updating a BFILE by Initializing a BFILE Locator

- Closing a BFILE with FILECLOSE

- Closing a BFILE with CLOSE

- Closing All Open BFILEs with FILECLOSEALL

- Inserting a Row Containing a BFILE

## Supported Environments for BFILE APIs

Table 15-1, "Environments Supported for BFILE APIs" indicates which programmatic environments are supported for the APIs discussed in this chapter. The first column describes the operation that the API performs. The remaining columns indicate with "Yes" or "No" whether the API is supported in PL/SQL, OCI, COBOL, Pro*C/C++, Visual Basic (VB), and JDBC.

Table 15-1 Environments Supported for BFILE APIs

Operation | PL/SQL | OCI | COBOL | Pro*C/C++ | VB | JDBC
--- | --- | --- | --- | --- | --- | ---
Inserting a Row Containing a BFILE | Yes | Yes | Yes | Yes | Yes | Yes
Loading a LOB with BFILE Data | Yes | Yes | Yes | Yes | Yes | Yes
Opening a BFILE with FILEOPEN | Yes | Yes | No | No | No | Yes
Opening a BFILE with OPEN | Yes | Yes | Yes | Yes | Yes | Yes
Determining Whether a BFILE Is Open Using ISOPEN | Yes | Yes | Yes | Yes | Yes | Yes
Determining Whether a BFILE Is Open with FILEISOPEN | Yes | Yes | No | No | No | Yes
Displaying BFILE Data | Yes | Yes | Yes | Yes | Yes | Yes
Reading Data from a BFILE | Yes | Yes | Yes | Yes | Yes | Yes
Reading a Portion of BFILE Data Using SUBSTR | Yes | No | Yes | Yes | Yes | Yes
Comparing All or Parts of Two BFILES | Yes | No | Yes | Yes | Yes | Yes
Checking If a Pattern Exists in a BFILE Using INSTR | Yes | No | Yes | Yes | No | Yes
Determining Whether a BFILE Exists | Yes | Yes | Yes | Yes | Yes | Yes
Getting the Length of a BFILE | Yes | Yes | Yes | Yes | Yes | Yes
Assigning a BFILE Locator | Yes | Yes | Yes | Yes | No | Yes
Getting Directory Object Name and File Name of a BFILE | Yes | Yes | Yes | Yes | Yes | Yes
Updating a BFILE by Initializing a BFILE Locator | Yes | Yes | Yes | Yes | Yes | Yes
Closing a BFILE with FILECLOSE | Yes | Yes | No | No | Yes | Yes
Closing a BFILE with CLOSE | Yes | Yes | Yes | Yes | Yes | Yes
Closing All Open BFILEs with FILECLOSEALL | Yes | Yes | Yes | Yes | Yes | Yes

## Accessing BFILEs

To access `BFILE`s use one of the following interfaces:

- OCI (Oracle Call Interface)

- PL/SQL (DBMS_LOB package)

- Precompilers, such as Pro*C/C++ and Pro*COBOL

- Oracle Objects for OLE (OO4O)

- Java (JDBC)

See Also: Chapter 6, "Overview of Supplied LOB APIs" for information about supported environments for accessing `BFILE`s.

## Directory Objects

The `DIRECTORY` object facilitates administering access and usage of `BFILE` datatypes. A `DIRECTORY` object specifies a logical alias name for a physical directory on the database server file system under which the file to be accessed is located. You can access a file in the server file system only if granted the required access privilege on `DIRECTORY` object. You can also use Enterprise Manager Web console to manage `DIRECTORY` objects.

See Also: `CREATE` `DIRECTORY` in Oracle Database SQL Reference support for directory objects in Oracle Enterprise Manager Extending Oracle Enterprise

### Initializing a BFILE Locator

The `DIRECTORY` object also provides the flexibility to manage the locations of the files, instead of forcing you to hard-code the absolute path names of physical files in your applications. A directory object name is used in conjunction with the `BFILENAME` function, in SQL and PL/SQL, or the `OCILobFileSetName()` in OCI, for initializing a `BFILE` locator.

Note: The database does not verify that the directory and path name you specify actually exist. You should take care to specify a valid directory in your operating system. If your operating system uses case-sensitive path names, then be sure you specify the directory in the correct format. There is no need to specify a terminating slash (for example, `/tmp/` is not necessary, simply use `/tmp`). Directory specifications cannot contain ".." anywhere in the path (for example, `/abc/def/hij..`).

### How to Associate Operating System Files with a BFILE

To associate an operating system file to a `BFILE`, first create a `DIRECTORY` object which is an alias for the full path name to the operating system file.

To associate existing operating system files with relevant database records of a particular table use Oracle SQL DML (Data Manipulation Language). For example:

- Use `INSERT` to initialize a `BFILE` column to point to an existing file in the server file system

- Use `UPDATE` to change the reference target of the `BFILE`

- Initialize a `BFILE` to `NULL` and then update it later to refer to an operating system file using the `BFILENAME` function.

- OCI users can also use `OCILobFileSetName()` to initialize a `BFILE` locator variable that is then used in the `VALUES` clause of an `INSERT` statement.

Directory Example

The following statements associate the files `Image1.gif` and `image2.gif` with records having `key_value` of 21 and 22 respectively. '`IMG`' is a `DIRECTORY` object that represents the physical directory under which `Image1.gif` and `image2.gif` are stored.

You may need to set up data structures similar to the following for certain examples to work:

    CREATE TABLE Lob_table (
       Key_value NUMBER NOT NULL,
       F_lob BFILE)
       INSERT INTO Lob_table VALUES
          (21,  BFILENAME('IMG', 'Image1.gif'));
       INSERT INTO Lob_table VALUES
          (22, BFILENAME('IMG', 'image2.gif'));

The following `UPDATE` statement changes the target file to `image3.gif` for the row with `key_value` of 22.

       UPDATE Lob_table SET f_lob = BFILENAME('IMG', 'image3.gif')
           WHERE Key_value = 22;

Note: The database does not expand environment variables specified in the DIRECTORY object or file name of a `BFILE` locator. For example, specifying:
    BFILENAME('WORK_DIR', '$MY_FILE')
where `MY_FILE`, an environment variable defined in the operating system, is not valid.

## BFILENAME and Initialization

`BFILENAME` is a built-in function that you use to initialize a `BFILE` column to point to an external file.

Once physical files are associated with records using SQL DML, subsequent read operations on the `BFILE` can be performed using PL/SQL `DBMS_LOB` package and OCI. However, these files are read-only when accessed through `BFILE`s, and so they cannot be updated or deleted through `BFILE`s.

As a consequence of the reference-based semantics for `BFILEs`, it is possible to have multiple `BFILE` columns in the same record or different records referring to the same file. For example, the following `UPDATE` statements set the `BFILE` column of the row with `key_value` =1 in `lob_table` to point to the same file as the row with `key_value` =22.

    UPDATE lob_table
       SET f_lob = (SELECT f_lob FROM lob_table WHERE key_value = 22)
          WHERE key_value = 21;

Think of `BFILENAME` in terms of initialization — it can initialize the value for the following:

- `BFILE` column

- `BFILE` (automatic) variable declared inside a PL/SQL module

## Characteristics of the BFILE Datatype

Using the `BFILE` datatype has the following advantages:

- If your need for a particular `BFILE` is temporary and limited within the module on which you are working, then you can use the `BFILE` related APIs on the variable without ever having to associate this with a column in the database.

- Because you are not forced to create a `BFILE` column in a server side table, initialize this column value, and then retrieve this column value using a `SELECT`, you save a round-trip to the server.

For more information, refer to the example given for `DBMS_LOB`.`LOADFROMFILE` (see "Loading a LOB with BFILE Data").

The OCI counterpart for `BFILENAME` is `OCILobFileSetName()`, which can be used in a similar fashion.

### DIRECTORY Name Specification

You must have `CREATE ANY DIRECTORY` system privilege to create directories. Path names cannot contain two dots (".."). The naming convention for `DIRECTORY` objects is the same as that for tables and indexes. That is, normal identifiers are interpreted in uppercase, but delimited identifiers are interpreted as is. For example, the following statement:

    CREATE OR REPLACE DIRECTORY scott_dir AS '/usr/home/scott';

creates or redefines a directory object whose name is '`SCOTT_DIR`' (in uppercase). But if a delimited identifier is used for the `DIRECTORY` name, as shown in the following statement

    CREATE DIRECTORY "Mary_Dir" AS '/usr/home/mary';

then the directory object name is '`Mary_Dir`'. Use '`SCOTT_DIR`' and '`Mary_Dir`' when calling `BFILENAME`. For example:

    BFILENAME('SCOTT_DIR', 'afile')
    BFILENAME('Mary_Dir', 'afile')

#### On Windows Platforms

On Windows platforms the directory names are case-insensitive. Therefore the following two statements refer to the same directory:

    CREATE DIRECTORY "big_cap_dir" AS "g:\data\source";

    CREATE DIRECTORY "small_cap_dir" AS "G:\DATA\SOURCE";

## BFILE Security

This section introduces the `BFILE` security model and associated SQL statements. The main SQL statements associated with `BFILE` security are:

- SQL DDL: `CREATE` and `REPLACE` or `ALTER` a `DIRECTORY` object

- SQL DML: `GRANT` and `REVOKE` the `READ` system and object privileges on `DIRECTORY` objects

### Ownership and Privileges

The `DIRECTORY` object is a system owned object. For more information on system owned objects, see Oracle Database SQL Reference. Oracle Database supports two new system privileges, which are granted only to DBA:

- `CREATE` `ANY` `DIRECTORY` — for creating or altering the directory object creation

- `DROP` `ANY` `DIRECTORY` — for deleting the directory object

### Read Permission on a DIRECTORY Object

`READ` permission on the `DIRECTORY` object enables you to read files located under that directory. The creator of the `DIRECTORY` object automatically earns the `READ` privilege.

If you have been granted the `READ` permission with `GRANT` option, then you may in turn grant this privilege to other users/roles and add them to your privilege domains.

Note: The `READ` permission is defined only on the `DIRECTORY` object, not on individual files. Hence there is no way to assign different privileges to files in the same directory.

The physical directory that it represents may or may not have the corresponding operating system privileges (read in this case) for the Oracle Server process.

It is the responsibility of the DBA to ensure the following:

- That the physical directory exists

- Read permission for the Oracle Server process is enabled on the file, the directory, and the path leading to it

- The directory remains available, and read permission remains enabled, for the entire duration of file access by database users

The privilege just implies that as far as the Oracle Server is concerned, you may read from files in the directory. These privileges are checked and enforced by the PL/SQL `DBMS_LOB` package and OCI APIs at the time of the actual file operations.

Caution: Because `CREATE` `ANY` `DIRECTORY` and `DROP` `ANY` `DIRECTORY` privileges potentially expose the server file system to all database users, the DBA should be prudent in granting these privileges to normal database users to prevent security breach.

### SQL DDL for BFILE Security

Refer to the Oracle Database SQL Reference for information about the following SQL DDL statements that create, replace, and drop directory objects:

- `CREATE` `DIRECTORY`

- `DROP` `DIRECTORY`

### SQL DML for BFILE Security

Refer to the Oracle Database SQL Reference for information about the following SQL DML statements that provide security for `BFILE`s:

- `GRANT` (system privilege)

- `GRANT` (object privilege)

- `REVOKE` (system privilege)

- `REVOKE` (object privilege)

- `AUDIT` (new statements)

- `AUDIT` (schema objects)

### Catalog Views on Directories

Catalog views are provided for DIRECTORY objects to enable users to view object names and corresponding paths and privileges. Supported views are:

- `ALL_DIRECTORIES` (`OWNER`, `DIRECTORY_NAME`, `DIRECTORY_PATH`)

  This view describes all directories accessible to the user.

- `DBA_DIRECTORIES`(`OWNER`, `DIRECTORY_NAME`, `DIRECTORY_PATH`)

  This view describes all directories specified for the entire database.

### Guidelines for DIRECTORY Usage

The main goal of the `DIRECTORY` feature is to enable a simple, flexible, non-intrusive, yet secure mechanism for the DBA to manage access to large files in the server file system. But to realize this goal, it is very important that the DBA follow these guidelines when using `DIRECTORY` objects:

- Do not map a `DIRECTORY` object to a data file directory. A `DIRECTORY` object should not be mapped to physical directories that contain Oracle data files, control files, log files, and other system files. Tampering with these files (accidental or otherwise) could corrupt the database or the server operating system.

- Only the DBA should have system privileges. The system privileges such as `CREATE` `ANY` `DIRECTORY` (granted to the DBA initially) should be used carefully and not granted to other users indiscriminately. In most cases, only the database administrator should have these privileges.

- Use caution when granting the DIRECTORY privilege. Privileges on `DIRECTORY` objects should be granted to different users carefully. The same holds for the use of the `WITH` `GRANT` `OPTION` clause when granting privileges to users.

- Do not drop or replace `DIRECTORY` objects when database is in operation. `DIRECTORY` objects should not be arbitrarily dropped or replaced when the database is in operation. If this were to happen, then operations from all sessions on all files associated with this directory object will fail. Further, if a `DROP` or `REPLACE` command is executed before these files could be successfully closed, then the references to these files will be lost in the programs, and system resources associated with these files will not be released until the session(s) is shut down.

  The only recourse left to PL/SQL users, for example, will be to either run a program block that calls `DBMS_LOB`.`FILECLOSEALL` and restart their file operations, or exit their sessions altogether. Hence, it is imperative that you use these commands with prudence, and preferably during maintenance downtimes.

- Use caution when revoking a user's privilege on `DIRECTORY` objects. Revoking a user's privilege on a `DIRECTORY` object using the `REVOKE` statement causes all subsequent operations on dependent files from the user's session to fail. Either you must re-acquire the privileges to close the file, or run a `FILECLOSEALL` in the session and restart the file operations.

In general, using `DIRECTORY` objects for managing file access is an extension of system administration work at the operating system level. With some planning, files can be logically organized into suitable directories that have READ privileges for the Oracle process.

`DIRECTORY` objects can be created with `READ` privileges that map to these physical directories, and specific database users granted access to these directories.

### BFILEs in Shared Server (Multithreaded Server) Mode

The database does not support session migration for `BFILE` datatypes in shared server (multithreaded server) mode. This implies that operations on open `BFILE` instances can persist beyond the end of a call to a shared server.

In shared server sessions, `BFILE` operations will be bound to one shared server, they cannot migrate from one server to another. This restriction will be removed in a forthcoming release.

### External LOB (BFILE) Locators

For `BFILE`s, the value is stored in a server-side operating system file; in other words, external to the database. The `BFILE` locator that refers to that file is stored in the row.

#### When Two Rows in a BFILE Table Refer to the Same File

If a `BFILE` locator variable that is used in a `DBMS_LOB`.`FILEOPEN` (for example L1) is assigned to another locator variable, (for example L2), then both L1 and L2 point to the same file. This means that two rows in a table with a `BFILE` column can refer to the same file or to two distinct files — a fact that the canny developer might turn to advantage, but which could well be a pitfall for the unwary.

#### BFILE Locator Variable

`A BFILE` locator variable operates like any other automatic variable. With respect to file operations, it operates like a file descriptor available as part of the standard input/output library of most conventional programming languages. This implies that once you define and initialize a `BFILE` locator, and open the file pointed to by this locator, all subsequent operations until the closure of this file must be done from within the same program block using this locator or local copies of this locator.

#### Guidelines for BFILEs

Note the following guidelines when working with `BFILE`s:

- Open and close a file from the same program block at same nesting level. The `BFILE` locator variable can be used, just as any scalar, as a parameter to other procedures, member methods, or external function callouts. However, it is recommended that you open and close a file from the same program block at the same nesting level.

- Set the `BFILE` value before flushing the object to the database. If an object contains a `BFILE`, then you must set the `BFILE` value before flushing the object to the database, thereby inserting a new row. In other words, you must call `OCILobFileSetName()` after `OCIObjectNew()` and before `OCIObjectFlush()`.

- Indicate the `DIRECTORY` object name and file name before inserting or updating of a `BFILE`. It is an error to insert or update a `BFILE` without indicating a directory object name and file name.

  This rule also applies to users using an OCI bind variable for a `BFILE` in an insert or update statement. The OCI bind variable must be initialized with a directory object name and file name before issuing the insert or update statement.

- Initialize `BFILE` Before insert or update

  Note: `OCISetAttr()` does not allow the user to set a `BFILE` locator to `NULL`.

- Before using SQL to insert or update a row with a `BFILE`, you must initialize the `BFILE` to one of the following:

  - `NULL` (not possible if using an OCI bind variable)

  - A directory object name and file name

- A path name cannot contain two dots ("..") anywhere in its specification. A file name cannot start with two dots.

## Loading a LOB with BFILE Data

This section describes how to load a LOB with data from a `BFILE`.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Preconditions

The following preconditions must exist before calling this procedure:

- The source `BFILE` instance must exist.

- The destination LOB instance must exist.

Usage Notes

Note: The `LOADBLOBFROMFILE` and `LOADCLOBFROMFILE` procedures implement the functionality of this procedure and provide improved features for loading binary data and character data. The improved procedures are available in the PL/SQL environment only. When possible, using one of the improved procedures is recommended. See "Loading a BLOB with Data from a BFILE" and "Loading a CLOB or NCLOB with Data from a BFILE" for more information.

Character Set Conversion

In using OCI, or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another.

BFILE to CLOB or NCLOB: Converting From Binary Data to a Character Set

When you use the `DBMS_LOB.LOADFROMFILE` procedure to populate a `CLOB` or `NCLOB`, you are populating the LOB with binary data from the `BFILE`. No implicit translation is performed from binary data to a character set. For this reason, you should use the `LOADCLOBFROMFILE` procedure when loading text (see Loading a CLOB or NCLOB with Data from a BFILE).

See Also: Oracle Database Globalization Support Guide for character set conversion issues.

Amount Parameter

Note the following with respect to the `amount` parameter:

- `DBMS_LOB.LOADFROMFILE`

  If you want to load the entire `BFILE`, then pass the constant `DBMS_LOB.LOBMAXSIZE`. If you pass any other value, then it must be less than or equal to the size of the `BFILE`.

- `OCILobLoadFromFile()`

  If you want to load the entire `BFILE`, then you can pass the constant `UB4MAXVAL`. If you pass any other value, then it must be less than or equal to the size of the `BFILE`.

- `OCILobLoadFromFile2()`

  If you want to load the entire `BFILE`, then you can pass the constant `UB8MAXVAL`. If you pass any other value, then it must be less than or equal to the size of the `BFILE`.

  See Also: Table 14-2, "Maximum LOB Size for Load from File Operations" for details on the maximum value of the amount parameter.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — LOADFROMFILE

- C (OCI): Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations", for usage notes and examples. Chapter 16, "LOB Functions" — `OCILobLoadFromFile2()`.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, embedded SQL, and LOB LOAD precompiler directives.

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements" "Embedded SQL Statements and Directives"— LOB LOAD.

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBLOB.OraCLOB > METHODS > CopyFromBfile

- Java (JDBC) Oracle Database JDBC Developer's Guide and Reference): "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

Examples are provided in the following programmatic environments:

- PL/SQL (DBMS_LOB): `floaddat.sql`

- OCI: `floaddat.c`

- Visual Basic (OO4O): `floaddat.bas`

- Java (JDBC): No example.

## Opening a BFILE with OPEN

This section describes how to open a `BFILE` using the OPEN function.

Note: You can also open a `BFILE` using the `FILEOPEN` function; however, using the `OPEN` function is recommended for new development. Using the `FILEOPEN` function is described in Opening a BFILE with FILEOPEN.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL(DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — OPEN

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations", for usage notes. Chapter 16, section "LOB Functions" — `OCILobOpen()`, `OCILobClose()`.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB OPEN.

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB OPEN.

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > Open, and > OBJECTS > OraDynaset > METHODS > MoveFirst MoveLast MovePrevious MoveNext

- Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Scenario

These examples open an image in operating system file `ADPHOTO_DIR`.

Examples

Examples are provided in the following six programmatic environments:

- PL/SQL(DBMS_LOB): `fopen.sql`

- OCI: `fopen.c`

- Visual Basic (OO4O): `fopen.bas`

- Java (JDBC): `fopen.java`

## Opening a BFILE with FILEOPEN

This section describes how to open a `BFILE` using the `FILEOPEN` function.

Note: The `FILEOPEN` function is not recommended for new application development. The `OPEN` function is recommended for new development. See "Opening a BFILE with OPEN"

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Usage Notes for Opening a BFILE

While you can continue to use the older `FILEOPEN` form, Oracle strongly recommends that you switch to using `OPEN`, because this facilitates future extensibility.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — FILEOPEN, FILECLOSE

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations, for usage notes. Chapter 16, section "LOB Functions" — `OCILobFileOpen()`, `OCILobFileClose()`, `OCILobFileSetName()`.

- COBOL (Pro*COBOL): A syntax reference is not applicable in this release.

- C/C++ (Pro*C/C++): A syntax reference is not applicable in this release.

- Visual Basic (OO4O): A syntax reference is not applicable in this release.

- Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Scenario for Opening a BFILE

These examples open `keyboard_logo.jpg` in directory object `MEDIA_DIR`.

Examples

Examples are provided in the following four programmatic environments:

- PL/SQL (DBMS_LOB): `ffilopen.sql`

- OCI: `ffilopen.c`

- Java (JDBC): `ffilopen.java`

## Determining Whether a BFILE Is Open Using ISOPEN

This section describes how to determine whether a `BFILE` is open using ISOPEN.

Note: This function (`ISOPEN`) is recommended for new application development. The older `FILEISOPEN` function, described in "Determining Whether a BFILE Is Open with FILEISOPEN", is not recommended for new development.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — ISOPEN

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — `OCILobFileIsOpen()`.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB DESCRIBE ... ISOPEN.

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB DESCRIBE ... ISOPEN

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > IsOpen and > OBJECTS > OraDynaset

- Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

Examples are provided in the following six programmatic environments:

- PL/SQL (DBMS_LOB): `fisopen.sql`

- OCI: `fisopen.c`

- Visual Basic (OO4O): `fisopen.bas`

- Java (JDBC): `fisopen.java`

## Determining Whether a BFILE Is Open with FILEISOPEN

This section describes how to determine whether a `BFILE` is OPEN using the FILEISOPEN function.

Note: The `FILEISOPEN` function is not recommended for new application development. The `ISOPEN` function is recommended for new development. See Determining Whether a BFILE Is Open Using ISOPEN

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Usage Notes

While you can continue to use the older `FILEISOPEN` form, Oracle strongly recommends that you switch to using `ISOPEN`, because this facilitates future extensibility.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL(DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — FILEISOPEN

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — `OCILobFileIsOpen()`.

- COBOL (Pro*COBOL): A syntax reference is not applicable in this release.

- C/C++ (Pro*C/C++): A syntax reference is not applicable in this release.

- Visual Basic (OO4O): A syntax reference is not applicable in this release.

- Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Scenario

These examples query whether a `BFILE` associated with `ad_graphic` is open.

Examples

Examples are provided in the following programmatic environments:

- PL/SQL(DBMS_LOB): `ffisopen.sql`

- OCI: `ffisopen.c`

- Java (JDBC): `ffisopen.java`

## Displaying BFILE Data

This section describes how to display `BFILE` data.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — READ. Chapter 29, "DBMS_OUTPUT" - PUT_LINE

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — `OCILobFileOpen()`, `OCILobRead2()`.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB READ, DISPLAY.

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements" — READ

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > Read, and OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > PollingAmount, Offset, Status. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples

- Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

Examples are provided in six programmatic environments:

- PL/SQL (DBMS_LOB): `fdisplay.sql`

- OCI: `fdisplay.c`

- Java (JDBC): `fdisplay.java`

## Reading Data from a BFILE

This section describes how to read data from a `BFILE`.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Usage Notes

Note the following when using this operation.

Streaming Read in OCI

The most efficient way to read large amounts of `BFILE` data is by `OCILobRead2()` with the streaming mechanism enabled, and using polling or callback. To do so, specify the starting point of the read using the `offset` parameter as follows:

    ub8  char_amt =  0;
    ub8  byte_amt =  0;
    ub4  offset = 1000;

    OCILobRead2(svchp, errhp, locp, &byte_amt, &char_amt, offset, bufp, bufl,
                OCI_ONE_PIECE, 0, 0, 0, 0);

When using polling mode, be sure to look at the value of the `byte_amt` parameter after each `OCILobRead2()` call to see how many bytes were read into the buffer, because the buffer may not be entirely full.

When using callbacks, the `lenp` parameter, which is input to the callback, will indicate how many bytes are filled in the buffer. Be sure to check the `lenp` parameter during your callback processing because the entire buffer may not be filled with data (see the Oracle Call Interface Programmer's Guide.)

Amount Parameter

- When calling `DBMS_LOB.READ`, the amount parameter can be larger than the size of the data; however, the amount parameter should be less than or equal to the size of the buffer. In PL/SQL, the buffer size is limited to 32K.

- When calling `OCILobRead2()`, you can pass a value of 0 (zero) for the `byte_amt` parameter to read to the end of the `BFILE`.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — READ

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — `OCILobRead2()`.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB READ.

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB READ

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > Read, and OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > PollingAmount, Offset, Status. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples

- Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

Examples are provided in the following programmatic environments:

- PL/SQL (DBMS_LOB): `fread.sql`

- OCI: `fread.c`

- Visual Basic (OO4O): `fread.bas`

- Java (JDBC): `fread.java`

## Reading a Portion of BFILE Data Using SUBSTR

This section describes how to read portion of `BFILE` data using `SUBSTR`.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — SUBSTR

- OCI: A syntax reference is not applicable in this release.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB OPEN, LOB CLOSE. See PL/SQL DBMS_LOB.SUBSTR.

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB OPEN. See also PL/SQL DBMS_LOB.SUBSTR

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > Open, and OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > PollingAmount, Offset, Status. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples

- Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

Examples are provided in these five programmatic environments:

- PL/SQL (DBMS_LOB): `freadprt.sql`

- C (OCI): No example is provided with this release.

- Visual Basic (OO4O): `freadprt.bas`

- Java (JDBC): `freadprt.java`

## Comparing All or Parts of Two BFILES

This section describes how to compare all or parts of two `BFILE`s.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL(DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — COMPARE

- C (OCI): A syntax reference is not applicable in this release.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB OPEN. See PL/SQL DBMS_LOB.COMPARE.

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB OPEN. See PL/SQL DBMS_LOB.COMPARE.

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > Open, Compare, and OO4O Automation Server > OBJECTS > OraDatabase > PROPERTIES > Parameters. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples

- Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

Examples are provided in these five programmatic environments:

- PL/SQL(DBMS_LOB): `fcompare.sql`

- OCI: No example is provided with this release.

- Visual Basic (OO4O): `fcompare.bas`

- Java (JDBC): `fcompare.java`

## Checking If a Pattern Exists in a BFILE Using INSTR

This section describes how to determine whether a pattern exists in a `BFILE` using `INSTR`.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — INSTR

- C (OCI): A syntax reference is not applicable in this release.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB OPEN. See PL/SQL DBMS_LOB.INSTR.

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB OPEN. See PL/SQL DBMS_LOB.INSTR.

- Visual Basic (OO4O): A syntax reference is not applicable in this release.

- Java (JDBC) (Oracle Database JDBC Developer's Guide and Reference): Chapter 7, "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

These examples are provided in the following four programmatic environments:

- PL/SQL (DBMS_LOB): `fpattern.sql`

- OCI: No example is provided with this release.

- Visual Basic (OO4O): No example is provided with this release.

- Java (JDBC): `fpattern.java`

## Determining Whether a BFILE Exists

This procedure determines whether a `BFILE` locator points to a valid `BFILE` instance.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB) Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — FILEEXISTS

- C (OCI) Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — `OCILobFileExists()`.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB DESCRIBE ... FILEEXISTS.

- C/C++ (Pro*C/C++) Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB DESCRIBE ...GET FILEEXISTS

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > Exists, and OO4O Automation Server > OBJECTS > OraDatabase > PROPERTIES > Parameters. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples

- Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

The examples are provided in the following six programmatic environments:

- PL/SQL (DBMS_LOB): `fexists.sql`

- OCI: `fexists.c`

- Visual Basic (OO4O): `fexists.bas`

- Java (JDBC): `fexists.java`

## Getting the Length of a BFILE

This section describes how to get the length of a `BFILE`.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — GETLENGTH

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations". Chapter 16, section "LOB Functions" — `OCILobGetLength2()`.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB DESCRIBE ... GET LENGTH INTO ...

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB DESCRIBE ... GET LENGTH INTO ...

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > Size. See also OO4O Automation Server > OBJECTS > OraBfile > Examples

- Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

The examples are provided in six programmatic environments:

- PL/SQL (DBMS_LOB): `flength.sql`

- OCI: `flength.c`

- Visual Basic (OO4O): `flength.bas`

- Java (JDBC): `flength.java`

## Assigning a BFILE Locator

This section describes how to assign one `BFILE` locator to another.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

- SQL (Oracle Database SQL Reference): Chapter 7, "SQL Statements" — CREATE PROCEDURE

- PL/SQL (DBMS_LOB): Refer to Chapter 5, "Advanced Design Considerations" of this manual for information on assigning one lob locator to another.

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — `OCILobLocatorAssign()`.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB ASSIGN

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB ASSIGN

- Visual Basic (OO4O): A syntax reference is not applicable in this release.

- Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: Chapter 7, "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

The examples are provided in the following five programmatic environments:

- PL/SQL (DBMS_LOB): `fcopyloc.sql`

- OCI: `fcopyloc.c`

- Visual Basic: An example is not provided with this release.

- Java (JDBC): `fcopyloc.java`

## Getting Directory Object Name and File Name of a BFILE

This section describes how to get the directory object name and file name of a `BFILE`.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — FILEGETNAME

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — `OCILobFileGetName()`.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB DESCRIBE ...GET DIRECTORY ...

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB DESCRIBE ...GET DIRECTORY ...

- Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

Examples of this procedure are provided in the following programmatic environments:

- PL/SQL (DBMS_LOB): `fgetdir.sql`

- OCI: `fgetdir.c`

- Visual Basic (OO4O): `fgetdir.bas`

- Java (JDBC): `fgetdir.java`

## Updating a BFILE by Initializing a BFILE Locator

This section describes how to update a `BFILE` by initializing a `BFILE` locator.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB): See the (Oracle Database SQL Reference), Chapter 7, "SQL Statements" — UPDATE

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — `OCILobFileSetName()`.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — ALLOCATE. See also (Oracle Database SQL Reference), Chapter 7, "SQL Statements" — UPDATE

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives". See also (Oracle Database SQL Reference), Chapter 7, "SQL Statements" — UPDATE

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help

- Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > DirectoryName, FileName, and OO4O Automation Server > OBJECTS > OraDatabase > METHODS > ExecuteSQL. See also OO4O Automation Server > OBJECTS > OraBfile > Examples

- Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: Chapter 7, "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

The examples are provided in six programmatic environments:

- PL/SQL (DBMS_LOB): `fupdate.sql`

- OCI: `fupdate.c`

- Visual Basic (OO4O): `fupdate.bas`

- Java (JDBC): `fupdate.java`

## Closing a BFILE with FILECLOSE

This section describes how to close a `BFILE` with `FILECLOSE`.

Note: This function (`FILECLOSE`) is not recommended for new development. For new development, use the `CLOSE` function instead. See "Closing a BFILE with CLOSE" for more information.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB)(Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — FILEOPEN, FILECLOSE

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — `OCILobFileClose()`.

- COBOL (Pro*COBOL): A syntax reference is not applicable in this release.

- C/C++ (Pro*C/C++): A syntax reference is not applicable in this release.

- Visual Basic (OO4O): A syntax reference is not applicable in this release.

- Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

- PL/SQL (DBMS_LOB): `fclose_f.sql`

- OCI: `fclose_f.c`

- Visual Basic (OO4O): This operation is not supported in Visual Basic. Instead use "Closing a BFILE with CLOSE" as described .

- Java (JDBC): `fclose_f.java`

## Closing a BFILE with CLOSE

This section describes how to close a `BFILE` with the `CLOSE` function.

Note: This function (`CLOSE`) is recommended for new application development. The older `FILECLOSE` function, is not recommended for new development.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Usage Notes

Opening and closing a `BFILE` is mandatory. You must close the instance later in the session.

See Also: Opening a BFILE with OPEN Determining Whether a BFILE Is Open Using ISOPEN

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — CLOSE

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — `OCILobClose()`.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB CLOSE

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB CLOSE

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help

- Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > PROPERTIES > IsOpen. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples.

- Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

- PL/SQL (DBMS_LOB): `fclose_c.sql`

- OCI: `fclose_c.c`

- Visual Basic (OO4O): `fclose_c.bas`

- Java (JDBC): `fclose_c.java`

## Closing All Open BFILEs with FILECLOSEALL

This section describes how to close all open `BFILE`s.

You are responsible for closing any `BFILE` instances before your program terminates. For example, you must close any open `BFILE` instance before the termination of a PL/SQL block or OCI program.

You must close open BFILE instances even in cases where an exception or unexpected termination of your application occurs. In these cases, if a `BFILE` instance is not closed, then it is still considered open by the database. Ensure that your exception handling strategy does not allow BFILE instances to remain open in these situations.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment. "Setting Maximum Number of Open BFILEs"

Syntax

Use the following syntax references for each programmatic environment:

- PL/SQL (DBMS_LOB) (Oracle Database PL/SQL Packages and Types Reference): "DBMS_LOB" — FILECLOSEALL

- C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 16, section "LOB Functions" — `OCILobFileCloseAll()`.

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB FILE CLOSE ALL

- C/C++ (Pro*C/C++) (Pro*C/C++ Programmer's Guide): "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB FILE CLOSE ALL

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBFILE > METHODS > CloseAll. See also OO4O Automation Server > OBJECTS > OraBFILE > Examples

- Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: Chapter 7, "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

- PL/SQL (DBMS_LOB): `fclosea.sql`

- OCI: `fclosea.c`

- Visual Basic (OO4O): `fclosea.bas`

- Java (JDBC): `fclosea.java`

## Inserting a Row Containing a BFILE

This section describes how to insert a row containing a `BFILE` by initializing a `BFILE` locator.

See Also: Table 15-1, "Environments Supported for BFILE APIs", for a list of operations on `BFILE`s and APIs provided for each programmatic environment.

Usage Notes

You must initialize the `BFILE` locator bind variable to `NULL` or a directory object and file name before issuing the `INSERT` statement.

Syntax

See the following syntax references for each programmatic environment:

- SQL(Oracle Database SQL Reference, Chapter 7 "SQL Statements" — INSERT

- C (OCI) Oracle Call Interface Programmer's Guide: Chapter 7, "LOB and File Operations".

- COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, embedded SQL, and precompiler directives. See also Oracle Database SQL Reference, for related information on the SQL INSERT statement.

- C/C++ (Pro*C/C++) Pro*C/C++ Programmer's Guide: "Large Objects (LOBs)", "LOB Statements", "Embedded SQL Statements and Directives" — LOB FILE SET. See also (Oracle Database SQL Reference), Chapter 7 "SQL Statements" — INSERT

- Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > DirectoryName, FileName; and > OBJECTS > OraDynaset >

- METHODS > Update

- Java (JDBC) Oracle Database JDBC Developer's Guide and Reference: "Working With LOBs" — Creating and Populating a `BLOB` or `CLOB` Column.

Examples

Examples in the following programmatic environments are provided:

- PL/SQL (DBMS_LOB): `finsert.sql`

- OCI: `finsert.c`

- Visual Basic (OO4O): `finsert.bas`

- Java (JDBC): `finsert.java`